#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2017/11/6 9:49
# @Author  : Yunhao.Cao
# @File    : main.py
from peewee import *
from datetime import datetime

__author__ = 'Yunhao.Cao'

__ALL__ = []

database = MySQLDatabase('test_peewee',
                         **{'host': '192.168.118.250', 'password': '123456', 'port': 3306, 'user': 'intern'})


class Person(Model):
    id = BigIntegerField(primary_key=True)
    birthday = DateTimeField(null=True)
    name = CharField()

    class Meta:
        database = database
        db_table = 'person'


if __name__ == '__main__':
    d1930 = datetime(1930, 1, 1)
    d1960 = datetime(1960, 1, 1)
    d19741101 = datetime(1974, 11, 1)
    d1990 = datetime(1990, 1, 1)
    d19900201 = datetime(1990, 2, 1)

    # 增
    print("#########  add  #########")
    kitty = Person.create(name="Kitty", birthday=d19741101)  # type: Person
    print("add kitty successfully.")

    # 删
    print("#########  delete  #########")
    kitty1 = Person.get(name="Kitty")
    if kitty1 is not None:
        kitty1.delete_instance()
        print("kitty has been deleted.")
    else:
        print("kitty is not existed.")

    # ----------------- 查 -----------------
    print("#########  find  #########")
    # 单个查找
    grandma = Person.select().where(Person.name == "grandma").get()
    print(grandma.birthday)

    # 返回query，可迭代对象
    query = Person.select().where(Person.birthday <= d1990)
    for p in query:
        print("query item: {}".format(p.name))
    # get只会返回第一个符合要求的
    person = Person.get(Person.birthday <= d1990)
    print("get: {}".format(person.name))

    # 查找或创建。若无则创建，且flag=True
    kitty, flag = Person.get_or_create(name="Kitty", birthday=d19741101)  # type: Person
    print("flag: {}".format(flag))
    print(kitty.name)

    # ----------------- 改 -----------------
    print("#########  modify  #########")
    Bob = Person.get(Person.name == "Bob")
    print("old birthday: {}".format(Bob.birthday))
    Bob.birthday = d19900201
    # 保存以写入数据库
    Bob.save()

    Bob = Person.get(Person.name == "Bob")
    print("new birthday: {}".format(Bob.birthday))

    Bob.birthday = d1990
    Bob.save()

    Bob = Person.get(Person.name == "Bob")
    print("new new birthday: {}".format(Bob.birthday))

    # 关闭连接
    database.close()
